rsubmit;
data temp.daily; set crsp.dsf (keep = cusip date ret);
where 1985<year(date)<2018; proc sort; by date;
data vix; set cboe.cboe (keep = date vxo); proc sort; by date;
data vix; set vix; lvxo=lag(vxo); dvix=vxo-lvxo;
drop vxo lvxo; if year(date)>2017 then delete;
data ff; set ff.factors_daily (keep = date mktrf rf); mkt=mktrf;
drop mktrf; where 1985<year(date)<2018; proc sort; by date;
data temp.daily; merge temp.daily ff vix; by date;
eret=(1+ret)/(1+rf)-1; yearm=(year(date)-1986)*12+month(date);
drop ret rf date; proc sort; by cusip yearm; proc means; run;

rsubmit;
options nosource nonotes errors=0;
proc reg data=temp.daily outest=temp.best noprint;
model eret = mkt dvix / edf; by cusip yearm;
data temp.vix; set temp.best;
if _edf_+_p_<15 then delete;
year=floor((yearm-0.01)/12)+1986;
month=yearm-(year-1986)*12;
keep cusip year month dvix;
proc means; run;

rsubmit;
data delist; set crsp.mse
(keep = cusip date event dlret dlstcd);
where event="DELIST" and dlret<10 and 1985<year(date)<2018;
year=year(date); month=month(date); drop date;
data crsp; set crsp.msf (keep = cusip date ret prc shrout);
where 1985<year(date)<2018; year=year(date); month=month(date);
me=abs(prc)*shrout; price=abs(prc); drop date prc shrout vol;
proc sort data=delist; by cusip year month;
proc sort data=crsp; by cusip year month;
data crsp; merge crsp (in=in1) delist (in=in2);
by cusip year month; if in1; proc means; run;

data exch; set crsp.mse (keep = date cusip exchcd shrcd);
year=year(date); month=month(date); drop date;
proc sort data=exch; by cusip year month;
data crsp; merge crsp exch; by cusip year month;
data temp.crsp; set crsp; by cusip year month;
i=0; do while(i<2); lexchcd = lag(exchcd); lshrcd=lag(shrcd);
if cusip = lag(cusip) and exchcd = . then exchcd = lexchcd;
if cusip = lag(cusip) and shrcd = . then shrcd = lshrcd;
i+1; end; if last.month then output; drop i lexchcd lshrcd;
proc sort data=temp.crsp nodupkey; by cusip year month;
data temp.crsp; set temp.crsp;
if (nmiss(dlret)=1 or dlret=0) and 500<=dlstcd<600 and (exchcd ne 0 
and exchcd ne 3 and exchcd ne 33) then dlret=-0.3;
if (nmiss(dlret)=1 or dlret=0) and 500<=dlstcd<600 and
(exchcd=0 or exchcd=3 or exchcd=33) then dlret=-0.55;
if nmiss(ret)=1 and nmiss(dlret)=0 then return=dlret*100;
else return=ret*100; if shrcd ne 10 and shrcd ne 11 then delete;
drop event ret dlret dlstcd shrcd; proc means; run;

rsubmit;
options source notes errors=5;
data ivol; merge temp.crsp temp.vix;
by cusip year month; proc sort; by year month;
proc univariate data=ivol noprint; var dvix; by year month;
output out=decile pctlpts = 20 to 80 by 20 pctlpre=dec;
DATA ivol; MERGE ivol decile; BY year month;
  pdecile=1;
  IF dvix > DEC20 THEN pdecile=2;
  IF dvix > DEC40 THEN pdecile=3;
  IF dvix > DEC60 THEN pdecile=4;
  IF dvix > DEC80 THEN pdecile=5;
  if nmiss(dvix) then pdecile=.;
drop dec20 dec40 dec60 dec80; proc means; run;
proc sort data=ivol; by cusip year month;
data ivol; set ivol; lme=lag(me);
lcusip=lag(cusip); vixdecile=lag(pdecile);
if lcusip ne cusip then vixdecile=.;
if lcusip ne cusip then lme=.;
if year<1986 or year>2017 then delete;
proc sort data=ivol nodupkey;
by cusip year month return;
proc tabulate data=ivol format=5.3;
class vixdecile; var return;
table return, (vixdecile all)*mean; run;

rsubmit;
data temp.daily; set crsp.dsf (keep = cusip date ret);
where 1985<year(date)<2018; year=year(date); month=month(date);
proc sort; by cusip year month; proc means; run;

rsubmit;
options nosource nonotes errors=0;
data ivol; set ivol (keep = cusip year month vixdecile lme);
proc sort data=ivol; by cusip year month;
proc sort data=temp.daily; by cusip year month;
data temp.daily; merge temp.daily ivol;
by cusip year month; proc sort; by date vixdecile;
proc means data=temp.daily noprint;
var ret; by date vixdecile; weight lme;
output out=temp.fvix mean(ret)=ret;
proc means data=temp.fvix; proc sort; by date; run;
data temp.fvix; set temp.fvix; drop _type_ _freq_;
%macro a(many); %do i=1 %to 5;
data fvix&i; set temp.fvix; where vixdecile=&i;
vixret&i=ret*100; drop ret vixdecile;
proc sort data=fvix&i; by date;
%end; %mend a; %a(1);
data temp.fvixall; merge fvix1 fvix2 fvix3 fvix4 fvix5;
by date; year=year(date); month=month(date);
proc sort; by date; proc means; run;
proc download data=temp.fvixall
out=sasuser.factor25 (replace=yes); run;
endrsubmit;
PROC EXPORT DATA= SASUSER.FACTOR25 
OUTFILE= "C:\Sasha\Idiosyncratic Volatility\fvix input.xls"
DBMS=EXCEL REPLACE; SHEET="vix18vw"; RUN;

rsubmit;
data temp.daily; set crsp.dsf (keep = cusip date ret);
where 1989<year(date)<2018; proc sort; by date;
data vix; set cboe.cboe (keep = date vix); proc sort; by date;
data vix; set vix; lvix=lag(vix); dvix=vix-lvix; drop vix lvix;
if year(date)>2017 or nmiss(dvix) then delete;
data ff; set ff.factors_daily (keep = date mktrf rf); mkt=mktrf;
drop mktrf; where 1989<year(date)<2018; proc sort; by date;
data temp.daily; merge temp.daily ff vix; by date;
eret=(1+ret)/(1+rf)-1; yearm=(year(date)-1986)*12+month(date);
drop ret rf date; proc sort; by cusip yearm; proc means; run;

rsubmit;
options nosource nonotes errors=0;
proc reg data=temp.daily outest=temp.best noprint;
model eret = mkt dvix / edf; by cusip yearm;
data temp.vix500; set temp.best;
if _edf_+_p_<15 then delete;
year=floor((yearm-0.01)/12)+1986;
month=yearm-(year-1986)*12;
keep cusip year month dvix;
proc means; run;

rsubmit;
options source notes errors=5;
data ivol; merge temp.crsp temp.vix500;
by cusip year month; proc sort; by year month;
proc univariate data=ivol noprint; var dvix; by year month;
output out=decile pctlpts = 20 to 80 by 20 pctlpre=dec;
DATA ivol; MERGE ivol decile; BY year month;
  pdecile=1;
  IF dvix > DEC20 THEN pdecile=2;
  IF dvix > DEC40 THEN pdecile=3;
  IF dvix > DEC60 THEN pdecile=4;
  IF dvix > DEC80 THEN pdecile=5;
  if nmiss(dvix) then pdecile=.;
drop dec20 dec40 dec60 dec80; proc means; run;
proc sort data=ivol; by cusip year month;
data ivol; set ivol; lme=lag(me);
lcusip=lag(cusip); vixdecile=lag(pdecile);
if lcusip ne cusip then vixdecile=.;
if lcusip ne cusip then lme=.;
if year<1990 or year>2017 then delete;
proc sort data=ivol nodupkey;
by cusip year month return;
proc tabulate data=ivol format=5.3;
class vixdecile; var return;
table return, (vixdecile all)*mean; run;

rsubmit;
data temp.daily; set crsp.dsf (keep = cusip date ret);
where 1989<year(date)<2018; year=year(date); month=month(date);
proc sort; by cusip year month; proc means; run;

rsubmit;
options nosource nonotes errors=0;
data ivol; set ivol (keep = cusip year month vixdecile lme);
proc sort data=ivol; by cusip year month;
proc sort data=temp.daily; by cusip year month;
data temp.daily; merge temp.daily ivol;
by cusip year month; proc sort; by date vixdecile;
proc means data=temp.daily noprint;
var ret; by date vixdecile; weight lme;
output out=temp.fvix mean(ret)=ret;
proc means data=temp.fvix; proc sort; by date; run;
data temp.fvix; set temp.fvix; drop _type_ _freq_;
%macro a(many); %do i=1 %to 5;
data fvix&i; set temp.fvix; where vixdecile=&i;
vixret&i=ret*100; drop ret vixdecile;
proc sort data=fvix&i; by date;
%end; %mend a; %a(1);
data temp.fvixall; merge fvix1 fvix2 fvix3 fvix4 fvix5;
by date; year=year(date); month=month(date);
proc sort; by date; proc means; run;
proc download data=temp.fvixall
out=sasuser.factor25 (replace=yes); run;
endrsubmit;
PROC EXPORT DATA= SASUSER.FACTOR25 
OUTFILE= "C:\Sasha\Idiosyncratic Volatility\fvix input.xls"
DBMS=EXCEL REPLACE; SHEET="vix500vw"; RUN;

rsubmit;
proc sort data=temp.vix; by cusip year month;
data ivol; merge temp.crsp temp.vix; by cusip year month;
proc sort data=ivol; by year month;
proc univariate data=ivol noprint; var vix; by year month;
output out=decile pctlpts = 20 to 80 by 20 pctlpre=dec;
DATA ivol; MERGE ivol decile; BY year month;
  pdecile=1;
  IF vix > DEC20 THEN pdecile=2;
  IF vix > DEC40 THEN pdecile=3;
  IF vix > DEC60 THEN pdecile=4;
  IF vix > DEC80 THEN pdecile=5;
  if nmiss(vix) then pdecile=.;
drop dec20 dec40 dec60 dec80; 
proc univariate data=ivol noprint; var beta; by year month;
output out=decile pctlpts = 20 to 80 by 20 pctlpre=dec;
DATA ivol; MERGE ivol decile; BY year month;
  decile=1;
  IF beta > DEC20 THEN decile=2;
  IF beta > DEC40 THEN decile=3;
  IF beta > DEC60 THEN decile=4;
  IF beta > DEC80 THEN decile=5;
  if nmiss(beta) then decile=.;
drop dec20 dec40 dec60 dec80; proc means; run;
proc sort data=ivol; by cusip year month;
data ivol; set ivol; 
lcusip=lag(cusip); vixdecile=lag(pdecile);
lme=lag(me); betadecile=lag(decile);
if lcusip ne cusip then vixdecile=.;
if lcusip ne cusip then betadecile=.;
if lcusip ne cusip then lme=.;
if year<1986 then delete; proc means; run;
proc sort nodupkey; by cusip year month;
proc tabulate data=ivol format=5.3;
class vixdecile; var return;
table return, (vixdecile all)*mean; run;
proc tabulate data=ivol format=5.3;
class betadecile; var return;
table return, (betadecile all)*mean; run;

rsubmit;
proc sort data=ivol; by year month betadecile vixdecile;
proc means data=ivol noprint; var return;
by year month betadecile vixdecile;	weight lme;
output out=factor mean(return) = port;
%macro a(many); %do i=1 %to 5; %do j=1 %to 5;
data factor&i&j; set factor; where vixdecile=&i and betadecile=&j;
port&i&j=port; drop port vixdecile betadecile;
run; %end; %end; %mend a; %a(1);
data temp.factor;
merge factor11 factor12 factor13 factor14 factor15
factor21 factor22 factor23 factor24 factor25
factor31 factor32 factor33 factor34 factor35
factor41 factor42 factor43 factor44 factor45
factor51 factor52 factor53 factor54 factor55;
by year month; if nmiss(month) then delete;
if year<1986 or year>2009 then delete;
drop _freq_ _type_; proc means data=temp.factor; run;
proc download data=temp.factor
out=sasuser.factor25 (replace=yes); run;
endrsubmit;
PROC EXPORT DATA= SASUSER.FACTOR25 
OUTFILE= "C:\Sasha\Idiosyncratic Volatility\2009.xls"
DBMS=EXCEL REPLACE; SHEET="vixbetavw"; RUN;

rsubmit;
data ivol; set temp.vix; proc sort; by year month;
proc univariate data=ivol noprint; var vix; by year month;
output out=decile pctlpts = 20 to 80 by 20 pctlpre=dec;
DATA ivol; MERGE ivol decile; BY year month;
  pdecile=1;
  IF vix > DEC20 THEN pdecile=2;
  IF vix > DEC40 THEN pdecile=3;
  IF vix > DEC60 THEN pdecile=4;
  IF vix > DEC80 THEN pdecile=5;
  if nmiss(vix) then pdecile=.;
drop dec20 dec40 dec60 dec80; 
proc univariate data=ivol noprint; var beta; by year month;
output out=decile pctlpts = 20 to 80 by 20 pctlpre=dec;
DATA ivol; MERGE ivol decile; BY year month;
  decile=1;
  IF beta > DEC20 THEN decile=2;
  IF beta > DEC40 THEN decile=3;
  IF beta > DEC60 THEN decile=4;
  IF beta > DEC80 THEN decile=5;
  if nmiss(beta) then decile=.;
drop dec20 dec40 dec60 dec80; proc means; run;
proc sort data=ivol; by cusip year month;
data ivol; set ivol; vixdecile=lag2(pdecile);
lcusip=lag2(cusip); betadecile=lag2(decile);
if lcusip ne cusip then vixdecile=.;
if lcusip ne cusip then betadecile=.;
if year<1986 then delete; proc means; run;
proc sort nodupkey; by cusip year month;
proc tabulate data=ivol format=5.3;
class vixdecile; var beta;
table beta, (vixdecile all)*mean; run;
proc tabulate data=ivol format=5.3;
class betadecile; var vix;
table vix, (betadecile all)*mean; run;

rsubmit;
proc sort data=ivol; by year month mbdecile ivoldecile;
proc means data=ivol noprint;
var vix beta; by year month mbdecile ivoldecile;
output out=factor mean(vix beta) = vix beta;
%macro a(many); %do i=1 %to 5; %do j=1 %to 5;
data factor&i&j; set factor; where ivoldecile=&i and mbdecile=&j;
vix&i&j=vix; beta&i&j=beta; drop vix beta ivoldecile mbdecile;
run; %end; %end; %mend a; %a(1);
data temp.factor;
merge factor11 factor12 factor13 factor14 factor15
factor21 factor22 factor23 factor24 factor25
factor31 factor32 factor33 factor34 factor35
factor41 factor42 factor43 factor44 factor45
factor51 factor52 factor53 factor54 factor55;
by year month; if nmiss(month) then delete;
if year<1986 or year>2009 then delete;
drop _freq_ _type_; proc means data=temp.factor; run;
proc download data=temp.factor
out=sasuser.factor25 (replace=yes); run;
endrsubmit;
PROC EXPORT DATA= SASUSER.FACTOR25 
OUTFILE= "C:\Sasha\Idiosyncratic Volatility\2009.xls"
DBMS=EXCEL REPLACE; SHEET="vixbetamonlag"; RUN;
